if exists (select  1 from  sysobjects  where name ='users' And Type In ('S','U'))
drop table users;

if exists (select  1 from  sysobjects  where name ='tech' And Type In ('S','U'))
drop table tech;

if exists (select  1 from  sysobjects  where name ='songs' And Type In ('S','U'))
drop table songs;

if exists (select  1 from  sysobjects  where name ='signs' And Type In ('S','U'))
drop table signs;

if exists (select  1 from  sysobjects  where name ='revert' And Type In ('S','U'))
drop table revert;

if exists (select  1 from  sysobjects  where name ='pictures' And Type In ('S','U'))
drop table pictures;

if exists (select  1 from  sysobjects  where name ='message' And Type In ('S','U'))
drop table message;

if exists (select  1 from  sysobjects  where name ='journal' And Type In ('S','U'))
drop table journal;

if exists (select  1 from  sysobjects  where name ='categorys' And Type In ('S','U'))
drop table categorys;

if exists (select  1 from  sysobjects  where name ='album' And Type In ('S','U'))
drop table album;

--相册表
CREATE TABLE [album] (
	[album_id] [int] IDENTITY (1, 1) NOT NULL ,
	[album_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[album_url] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[album_face] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	CONSTRAINT [PK_album] PRIMARY KEY  CLUSTERED 
	(
		[album_id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO



--日志类别表 
CREATE TABLE [categorys] (
	[category_id] [int] IDENTITY (1, 1) NOT NULL ,
	[category_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	CONSTRAINT [PK_categorys] PRIMARY KEY  CLUSTERED 
	(
		[category_id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO



--日志表 
CREATE TABLE [journal] (
	[journal_id] [int] IDENTITY (1, 1) NOT NULL ,
	[journal_title] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[journal_content] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[journal_category] [int] NOT NULL ,
	[journal_top] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[journal_date] [datetime] NOT NULL ,
	CONSTRAINT [PK_journal] PRIMARY KEY  CLUSTERED 
	(
		[journal_id]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_journal_categorys] FOREIGN KEY 
	(
		[journal_category]
	) REFERENCES [categorys] (
		[category_id]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO






--留言表  
CREATE TABLE [message] (
	[message_id] [int] IDENTITY (1, 1) NOT NULL ,
	[message_content] [varchar] (500) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[message_date] [datetime] NOT NULL ,
	[message_user] [int] NULL ,
	CONSTRAINT [PK_message] PRIMARY KEY  CLUSTERED 
	(
		[message_id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO





--相片表  
CREATE TABLE [pictures] (
	[picture_id] [int] IDENTITY (1, 1) NOT NULL ,
	[picture_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[picture_url] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[picture_album] [int] NOT NULL ,
	[picture_height] [int] NOT NULL ,
	[picture_width] [int] NOT NULL ,
	[picture_date] [datetime] NOT NULL ,
	CONSTRAINT [PK_pictures] PRIMARY KEY  CLUSTERED 
	(
		[picture_id]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_pictures_album] FOREIGN KEY 
	(
		[picture_album]
	) REFERENCES [album] (
		[album_id]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
) ON [PRIMARY]
GO









--日志回复表  
CREATE TABLE [revert] (
	[revert_id] [int] IDENTITY (1, 1) NOT NULL ,
	[revert_content] [varchar] (500) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[revert_journal] [int] NOT NULL ,
	[revert_user] [int] NULL ,
	[revert_date] [datetime] NOT NULL ,
	CONSTRAINT [PK_revert] PRIMARY KEY  CLUSTERED 
	(
		[revert_id]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_revert_journal] FOREIGN KEY 
	(
		[revert_journal]
	) REFERENCES [journal] (
		[journal_id]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
) ON [PRIMARY]
GO



--签名表   
CREATE TABLE [signs] (
	[sign_id] [int] IDENTITY (1, 1) NOT NULL ,
	[sign_filename] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[sign_width] [int] NOT NULL ,
	[sign_height] [int] NOT NULL ,
	[sign_date] [datetime] NOT NULL ,
	CONSTRAINT [PK_signs] PRIMARY KEY  CLUSTERED 
	(
		[sign_id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO



--歌曲表  

CREATE TABLE [songs] (
	[song_id] [int] IDENTITY (1, 1) NOT NULL ,
	[song_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[song_singer] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
	[song_url] [varchar] (100) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[song_lrc] [ntext] COLLATE Chinese_PRC_CI_AS NULL ,
	[song_date] [datetime] NOT NULL ,
	CONSTRAINT [PK_songs] PRIMARY KEY  CLUSTERED 
	(
		[song_id]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



--java技术表  
CREATE TABLE [tech] (
	[tech_id] [int] IDENTITY (1, 1) NOT NULL ,
	[tech_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[tech_desc] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[tech_url] [varchar] (100) COLLATE Chinese_PRC_CI_AS  NULL ,
	[tech_adddate] [datetime] NOT NULL ,
	[tech_editdate] [datetime] NOT NULL ,
	CONSTRAINT [PK_tech] PRIMARY KEY  CLUSTERED 
	(
		[tech_id]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO



--用户表  
CREATE TABLE [users] (
	[user_id] [int] IDENTITY (1, 1) NOT NULL ,
	[user_name] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[user_password] [varchar] (24) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[user_picture] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_users_user_picture] DEFAULT ('default.jpg'),
	[user_realname] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
	[user_isadmin] [char] (1) COLLATE Chinese_PRC_CI_AS NOT NULL ,
	[user_date] [datetime] NOT NULL ,
	CONSTRAINT [PK_users] PRIMARY KEY  CLUSTERED 
	(
		[user_id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO







